<?php
namespace App\Http\Query;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Log;

/*
 * 商品情報
 *
 */
class GoodsQuery extends Model
{
    const TABLE_NAME = "goods_master";

    protected $table = 'goods_master';

    protected $primaryKey = 'goods_id';

    protected $GoodsMaster = self::TABLE_NAME;

    public $timestamps = false;

    // 取商品code(OK)
    public function getGoodsCode()
    {
        $sql = 'select goods_code from goods_master  WHERE delete_flg = 0 ';
        $goodsCode = DB::select($sql);
        return $goodsCode;
    }

    public function getCateCode()
    {
        $sql = 'select category_id,category_name from category_master  WHERE delete_flg = 0 ';
        $cataCode = DB::select($sql);
        return $cataCode;
    }

    public function getSuppCode()
    {
        $sql = 'select supplier_id ,supplier_name from supplier_master  WHERE delete_flg = 0 ';
        $cataCode = DB::select($sql);
        return $cataCode;
    }

    public function getGoods()
    {
        $sql = 'SELECT a.goods_id, a.goods_code, a.goods_name, a.goods_kana, a.category_id, b.category_name, a.price FROM goods_master AS a LEFT JOIN category_master AS b ON a.category_id = b.category_id WHERE a.delete_flg = 0 ';
        $goods = DB::select($sql);
        return $goods;
    }

    // 查商品資訊
    public function getGoodsInfoByGoodsCode($goodsCode)
    {
        $sql = 'select goods_code, goods_name, goods_kana, category_id, price, jancode, start_date,
                end_date, supplier_id, money_type, supply_price, new_flg, top_show_flg, web_open
                from goods_master where goods_code= ? and delete_flg = 0';
        $param = [
            $goodsCode
        ];
        $goodsInfo = DB::select($sql, $param);
        return $goodsInfo;
    }

    // 查商品資訊
    public function getGoodsInfoByGoodsid($goodsCode)
    {
        $sql = 'SELECT a.goods_id, a.goods_code, a.goods_name, a.goods_kana, a.category_id, a.price, a.jancode, a.start_date, a.end_date, a.supplier_id, a.money_type, a.supply_price, a.new_flg, a.top_show_flg, a.web_open, concat( b.image_url,b.file_name) filesrc ,a.min_lot
                FROM goods_master AS a LEFT JOIN goods_picture AS b ON a.goods_id = b.goods_id WHERE a.goods_id = ? AND a.delete_flg = 0';
        $param = [
            $goodsCode
        ];
        $goodsInfo = DB::select($sql, $param);
        return $goodsInfo;
    }

    // 查商品資訊
    public function getGoodsModelByGoodsId($goodsId)
    {
        $sql = 'SELECT * FROM goods_model WHERE delete_flg = 0 AND goods_id = ?';
        $param = [
            $goodsId
        ];
        $goodsModel = DB::select($sql, $param);
        return $goodsModel;
    }

    // 刪除
    public function deleteGoodsByGoodsCode($goodsCode)
    {
        DB::beginTransaction();

        try {
            $sql = ' update goods_master,goods_picture,goods_model set goods_master.delete_flg=1, goods_picture.delete_flg=1, goods_model.delete_flg=1
                        where goods_model.goods_id = ? and goods_master.goods_id = ? and goods_picture.goods_id = ?';
            $param = [
                $goodsCode,
                $goodsCode,
                $goodsCode
            ];
            DB::update($sql, $param);
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return false;
        }

        return true;
    }

    public function updateGoodsMaster($list, $data, $goodsInfo, $fileParam, $goodsModel, $lengthModelUpdate, $file, $goodsid, $goodsUpdateModel) // 改
    {
        DB::beginTransaction();
        try {
            if (!empty($file)) {
                Log::info($fileParam);
                $sql = 'UPDATE goods_picture  SET  file_name = ? , ext_name = ?, delete_flg = 0, created_at = now(), updated_at = now() WHERE delete_flg = 0 and goods_id = ?';
                DB::update($sql, $fileParam);
            }
            if(!empty($goodsModel)){
                $sqldel = 'delete from goods_model   WHERE  goods_id = ?';
                DB::update($sqldel, array($goodsid));

                $lengthSaveModel = count($goodsModel)/3;

                for($i = 0; $i < $lengthSaveModel; $i ++){
                    $sql = 'INSERT INTO  goods_model (goods_id, color, size, model, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
                    $modelParam = array(
                        $goodsid,
                        $goodsModel['color'. $i],
                        $goodsModel['size'. $i],
                        $goodsModel['model'. $i],
                    );
                    DB::insert($sql, $modelParam);
                }
            }

            $CorpMaster = $this->where($list, '=', $data);
            $CorpMaster->update($goodsInfo);

            DB::commit();
        } catch (\Exception $e) {
           // echo $e->getFile()." ".$e->getLine()." ".$e->getMessage();
            DB::rollBack();
            return false;
        }
        return true;
    }

    public function addGoodsMaster($goodsInfo)
    {
        $sql = 'INSERT INTO goods_master(goods_code, goods_name, goods_kana, category_id, price, jancode, start_date, end_date, supplier_id, money_type, supply_price, new_flg, top_show_flg, delete_flg, created_at, updated_at, web_open, min_lot)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,0,now(),now(),?,?)';

        $param = [
            $goodsInfo['goods_code'],
            $goodsInfo['goods_name'],
            $goodsInfo['goods_kana'],
            $goodsInfo['category_id'],
            $goodsInfo['price'],
            $goodsInfo['jancode'],
            $goodsInfo['start_date'],
            $goodsInfo['end_date'],
            $goodsInfo['supplier_id'],
            $goodsInfo['money_type'],
            $goodsInfo['supply_price'],
            $goodsInfo['new_flg'],
            $goodsInfo['top_show_flg'],
            $goodsInfo['web_open'],
            $goodsInfo['min_lot']
        ];

        return DB::insert($sql, $param);
    }


    // add picture model info
    public function addGoodsPictureModel($param, $goodsModel, $lengthModel)
    {
        DB::beginTransaction();
        try {

            Log::info($param);
            Log::info($goodsModel);
            $sql = 'INSERT INTO goods_picture(goods_id, image_url, file_name, ext_name, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
            DB::insert($sql, $param);

            for($i = 0; $i < $lengthModel; $i ++){

                $sql = 'INSERT INTO  goods_model (goods_id, color, size, model, delete_flg, created_at, updated_at) VALUES (?,?,?,?,0,now(),now())';
                $paramModel = [
                    $goodsModel['goods_id'],
                    $goodsModel['color'. $i],
                    $goodsModel['size'. $i],
                    $goodsModel['model'. $i]
                ];
                DB::insert($sql, $paramModel);
            }
            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return false;
        }

        return true;
    }

    // select goodsid by goodscode
    public function selectGoodsIdByGoodsCode($goodsCode)
    {
        $sql = 'SELECT goods_id FROM goods_master where delete_flg = 0 and goods_code = ? ';
        $param = [
            $goodsCode
        ];

        $goodsId = DB::select($sql, $param);

        return $goodsId;
    }



    // 改資訊
    public function saveGoodsByGoodsCode($goodsCode)
    {
        $sql = ' update * from goods_master  where goods_code = ? and delete_flg = 0';
        $param = [
            $goodsCode
        ];
        $goods = DB::select($sql, $param);
        return $goods;
    }

    // 取商品code(OK)
    public function csvSave($master, $tabNm)
    {
        DB::table($tabNm)->insert($master);
    }

    // 取商品code
    public function getCheckGoodsCode($goodsCode)
    {
        $sql = 'SELECT * FROM goods_master where delete_flg = 0 and goods_code = ? ';
        $param = [
            $goodsCode
        ];
        return DB::select($sql, $param);
    }
    /**
     * 新商品取得
     * @return array|mixed
     */
    public function getNewGoods()
    {
        $sql = 'select goods_code, goods_name, goods_kana, category_id, price, jancode, start_date,
                end_date, supplier_id, money_type, supply_price, new_flg, top_show_flg, web_open,
                pt.image_url,pt.file_name
                from goods_master gm left join (select g.goods_id,g.image_id,image_url,file_name from goods_picture  g join (
        SELECT goods_id,min(image_id)  as image_id FROM goods_picture  group by goods_id) m
        on m.goods_id = g.goods_id and m.image_id = g.image_id) pt on gm.goods_id = pt.goods_id  where new_flg = 1 and delete_flg = 0';

        $goodsInfo = DB::select($sql);

        $sql = "select g.goods_id,g.image_id,image_url from goods_picture  g
        join (
        SELECT goods_id,min(image_id)  as image_id FROM goods_picture  group by goods_id) m
        on m.goods_id = g.goods_id and m.image_id = g.image_id
        ";
        $picts = DB::select($sql);


        return $goodsInfo;
    }

    public function getGoodsJson($goodsCode)
    {
        $sql = 'SELECT goods_code as id, goods_name as name, goods_id as goodsid FROM goods_master where delete_flg = 0 and goods_code like concat(?,"%")';
        $param = [$goodsCode];
        return DB::select($sql, $param);
    }

    public function getCheckGoodsJson($goodsCode)
    {
        $sql = 'SELECT goods_code FROM goods_master where delete_flg = 0 and goods_code = ?';
        $param = [$goodsCode];
        return DB::select($sql, $param);
    }
    public function deleteUpdateGoodsJson($modelId)
    {
        $sql = 'UPDATE goods_model  SET  delete_flg =1 WHERE goods_model_id = ?';
        $param = [
            $modelId
        ];
        return  DB::update($sql, $param);
    }


}
